/*
 * Aboboo Android
 * Copyright (C) 2012 www.aboboo.com
 * 
 */
package com.aboboo.android.database;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import android.content.Context;
import android.content.ContentValues;
import android.util.Log;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.SQLException;
import com.aboboo.android.database.AbobooSQLHelper;
import com.aboboo.android.database.DwCourseware;

/**
 * 封装数据库操作服务类
 * 
 * @author fangpeng 
 * version 1.0 2012-9-11
 * 
 */
public class AbobooDBService {
	private AbobooSQLHelper dbHelper;

	public AbobooDBService(Context context) {
		//Log.i("AbobooDBService", "初始化数据库服务");
		dbHelper = new AbobooSQLHelper(context);
	}

	/**
	 * 时间日期格式化
	 * 
	 * @param d
	 * @return
	 */
	public String formatMillisTimeToDateString(long millisTime) {
		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		return formatter.format(new Date(millisTime)).trim();
	}

	/**
	 * 插入一条课件记录信息
	 */
	public void cwInsert(DwCourseware courseware) {
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		final String sql = "INSERT INTO dw_courseware(courseware_id, group_name, "
				+ "group_id, name, class1, class2, fileLength, download_url, "
				+ "sd_filePath, sd_fileName, dw_date, recent_study_date) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
		db.execSQL(
				sql,
				new Object[] { courseware.getCourseware_id(),
						courseware.getGroup_name(), courseware.getGroup_id(),
						courseware.getName(), courseware.getClass1(),
						courseware.getClass2(), courseware.getFileLength(),
						courseware.getDownload_url(),
						courseware.getSd_filePath(),
						courseware.getSd_fileName(), courseware.getDw_date(),
						courseware.getRecent_study_date() });
		db.close();
	}

	/**
	 * 更新课件信息,最近学习时间
	 * 
	 * @param courseware_id
	 */
	public void cwUpdate(int courseware_id, long studyTime) {
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.execSQL(
				"UPDATE dw_courseware SET recent_study_date=? WHERE courseware_id=?",
				new Object[] { studyTime, courseware_id });
		db.close();
	}

	/**
	 * 删除课件信息
	 * 
	 * @param courseware_id
	 */
	public void cwDelete(int courseware_id) {
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.execSQL("DELETE FROM dw_courseware WHERE courseware_id=?",
				new Object[] { courseware_id });
		db.close();
	}

	/**
	 * 根据查询游标构造一个课件信息
	 * 
	 * @return
	 */
	private DwCourseware createDwCoursewareObj(Cursor cursor) {
		DwCourseware cw = new DwCourseware();
		cw.setCourseware_id(cursor.getInt(cursor
				.getColumnIndex("courseware_id")));
		cw.setGroup_id(cursor.getInt(cursor.getColumnIndex("group_id")));
		cw.setGroup_name(cursor.getString(cursor.getColumnIndex("group_name")));
		cw.setName(cursor.getString(cursor.getColumnIndex("name")));
		cw.setClass1(cursor.getString(cursor.getColumnIndex("class1")));
		cw.setClass2(cursor.getString(cursor.getColumnIndex("class2")));
		cw.setFileLength(cursor.getInt(cursor.getColumnIndex("fileLength")));
		cw.setDownload_url(cursor.getString(cursor
				.getColumnIndex("download_url")));
		cw.setSd_filePath(cursor.getString(cursor.getColumnIndex("sd_filePath")));
		cw.setSd_fileName(cursor.getString(cursor.getColumnIndex("sd_fileName")));
		cw.setDw_date(cursor.getLong(cursor.getColumnIndex("dw_date")));
		cw.setRecent_study_date(cursor.getLong(cursor
				.getColumnIndex("recent_study_date")));
		return cw;
	}

	/**
	 * 查询所有已经下载的课件信息
	 */
	public ArrayList<DwCourseware> cwQueryAll() {
		ArrayList<DwCourseware> cw_list = new ArrayList<DwCourseware>();
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery("SELECT * FROM dw_courseware", null);
		while (cursor.moveToNext()) {
			cw_list.add(createDwCoursewareObj(cursor));
		}
		cursor.close();
		db.close();
		return cw_list;
	}

	/**
	 * 根据课件ID查询课件信息
	 * 
	 * @param courseware_id
	 */
	public DwCourseware cwQueryById(int courseware_id) {
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery(
				"SELECT * FROM dw_courseware WHERE courseware_id=?",
				new String[] { String.valueOf(courseware_id) });
		if (cursor.moveToFirst()) {
			return createDwCoursewareObj(cursor);
		}
		cursor.close();
		db.close();
		return null;
	}

	/**
	 * 根据课件组查询课件信息
	 * 
	 * @param group_id
	 */
	public ArrayList<DwCourseware> cwQueryByGroupId(String group_id) {
		ArrayList<DwCourseware> cw_list = new ArrayList<DwCourseware>();
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery(
				"SELECT * FROM dw_courseware WHERE group_id=?",
				new String[] { group_id });
		while (cursor.moveToNext()) {
			cw_list.add(createDwCoursewareObj(cursor));
		}
		cursor.close();
		db.close();
		return cw_list;
	}

	/**
	 * 查询最近学习的课件信息
	 * 
	 * @return
	 */
	public ArrayList<DwCourseware> cwRecentStudy() {
		ArrayList<DwCourseware> cw_list = new ArrayList<DwCourseware>();
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db
				.rawQuery(
						"SELECT * FROM dw_courseware ORDER BY recent_study_date DESC limit 5",
						null);
		while (cursor.moveToNext()) {
			cw_list.add(createDwCoursewareObj(cursor));
		}
		cursor.close();
		db.close();
		return cw_list;
	}

	/**
	 * 所有已经下载的课件数量
	 * 
	 * @return
	 */
	public int getAllCwAmount() {
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery("SELECT COUNT(*) FROM dw_courseware", null);
		cursor.moveToFirst();
		int amount = cursor.getInt(0);
		cursor.close();
		db.close();
		return amount;
	}

	/**
	 * 课件组下的课件数量
	 * 
	 * @return
	 */
	public int getCwAmountByGroup(String group_id) {
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery(
				"SELECT COUNT(*) FROM dw_courseware WHERE group_id=?",
				new String[] { group_id });
		cursor.moveToFirst();
		int amount = cursor.getInt(0);
		cursor.close();
		db.close();
		return amount;
	}
}
